# coding:utf-8

import xlrd
import xlwt
from  xlwt import *
# workbook相关
from openpyxl.workbook import Workbook
# ExcelWriter，封装了很强大的excel写的功能
from openpyxl.writer.excel import ExcelWriter
# 一个eggache的数字转为列字母的方法
from openpyxl.utils import get_column_letter
from openpyxl.reader.excel import load_workbook
import pymysql
import time
import datetime
import arrow
import sys
import decimal
from decimal import Decimal
context=decimal.getcontext() # 获取decimal现在的上下文
context.rounding = decimal.ROUND_05UP


reload(sys)
sys.setdefaultencoding('utf8')

s=datetime.datetime.now()

mysqlConfig1={
    "host" : '192.168.0.34',
    "port" : 3306,
    "dbname" : 'result',
    "user" : 'root',
    "passwd": 'root'
}
mysqlConfig2={
    "host" : '192.168.0.31',
    "port" : 3306,
    "dbname" : 'oracleanditem',
    "user" : 'root',
    "passwd": 'root'
}
mysqlConfig3={
    "host" : '192.168.0.31',
    "port" : 3306,
    "dbname" : 'oraclecd',
    "user" : 'root',
    "passwd": 'root'
}


now = arrow.now()
today = now.strftime("%Y-%m-%d")
yesterday = (now + datetime.timedelta(days=-1, hours=-0.5)).strftime("%Y-%m-%d")  ##c为str（即字符串），将时间格式化成字符串
fname = (now + datetime.timedelta(days=-1, hours=-0.5)).strftime("%Y%m%d")  ##c为str（即字符串），将时间格式化成字符串
fname=u'/home/marvin/daily_job/py_job/'+fname+'.xls'
monthago = (now.shift(hours=-24).shift(months=-1)).strftime("%Y-%m-%d")  ##c为str（即字符串），将时间格式化成字符串
dayb7 = (now + datetime.timedelta(days=-7, hours=-0.5)).strftime("%Y-%m-%d")  ##c为str（即字符串），将时间格式化成字符串
dayb8 = (now + datetime.timedelta(days=-8, hours=-0.5)).strftime("%Y-%m-%d")  ##c为str（即字符串），将时间格式化成字符串
print today, yesterday,dayb7,dayb8,monthago
a=arrow.now()
print a.shift(hours=-24)
lastmonth=(now.shift(hours=-24).shift(months=-1)).datetime.month
themonth=(now.shift(hours=-24)).datetime.month
# 统计油环比 lit92 svp qiyou chaiyou ranyou
sql1='''
SELECT SUM( case when oils='92#' then oilLitre else 0 end) lit92
,SUM( case when oils='95#' or oils='98#' then oilLitre else 0 end) svp
,SUM( case when oils='92#' or oils='95#' or oils='98#' then oilLitre else 0 end) qiyou
,SUM( case when oils='0#' or oils='-10#' or oils='-20#' or oils='-35#' then oilLitre else 0 end) chaiyou
,SUM(  oilLitre) ranyou
from res_oil where   minutes>='%s' and minutes<'%s' AND stationID='%s'
'''

# 统计便利店环比
sql2='''
SELECT SUM(notoilMoney) from res_notoil where  minutes>='%s' and minutes<'%s' and stationID='%s'
'''

# 统计会员新增
sql3='''
SELECT COUNT(DISTINCT a.CARDUSERID) from 
(SELECT DISTINCT CARDUSERID,STATIONCODE from TBL_ORDERDETAILS where SENDTIME>='{t1}' and SENDTIME<'{t2}') as a 
join TBL_CARDUSER as b
on a.CARDUSERID=b.CARDUSER_ID 
WHERE b.RECORDTIME>='{t1}' and b.RECORDTIME<'{t2}' and a.STATIONCODE='{station}'
'''

sql4='''
select 
sum(oilLitre) oilLitre
from res_oil  ro
where stationID IN 
(SELECT id from station where type='{type}'  and city='{city}') and
		 minutes >= '{t1}'
and minutes< '{t2}'
'''



class HandleExcel():
    '''Excel相关操作类'''

    def __init__(self):
        self.head_row_labels = [u'transaction_date', u'station_id',
                                u'transaction_id',u'product_code' ,u'item_quantity',u'item_value']
    """
        function：
            设置单元格样式
        Param:
            name:  字体名字
            height:  字体高度
            bold:  是否大写
        Return:
            style: 返回设置好的格式对象
    """
    def set_style(self, name, height, bold=False,pattern_fore_colour=2):
        style = xlwt.XFStyle()  # 初始化样式
        font = xlwt.Font()  # 为样式创建字体
        font.name = name  # 'Times New Roman'
        font.bold = bold
        font.color_index = 4
        font.height = height
        style.font = font
        return style


    def write_to_excel2(self, dataset, save_excel_name):
        f = xlwt.Workbook()  # 创建工作簿
        # 创建第一个sheet:
        # sheet1
        count = 0
        sheet1 = f.add_sheet(u'sheet1', cell_overwrite_ok=True)  # 创建sheet

        # 首行标题：
        # for p in range(len(head_row)):
        #     sheet1.write(0, 2, head_row[p], self.set_style('Times New Roman', 300, True))
        default = self.set_style('Times New Roman', 250, True)  # define style out the loop will work
        for line in dataset:
            first_col = sheet1.col(2)
            first_col.width = 256 * 50
            sheet1.write(count, 2, line, default)
            count += 1
        f.save(save_excel_name)  # 保存文件

def calculateOil(cur,station,t1,t2):
    # 获取营业天数
    cur.execute('''
       SELECT count(DISTINCT SUBSTR(minutes,1,10)) FROM res_oil where stationID='%s' and minutes>='%s' and minutes<'%s'
       ''' % (station, t1,t2))
    # yingyedays=0
    try:
        yingyedays = cur.fetchall()[0][0]
        if yingyedays==0:
            return {}
    except:
        return {}

    # 计算上个月的总体指标
    cur.execute(sql1 % (t1,t2, station))
    zhibiao = cur.fetchall()
    lit92 = zhibiao[0][0]
    svp = zhibiao[0][1]
    qiyou = zhibiao[0][2]
    chaiyou = zhibiao[0][3]
    ranyou = zhibiao[0][4]
    # print sql1%(monthago[0:7],yesterday[0:7],'50009')
    # print zhibiao[0]

    # 计算平均指标
    avg_lit92 = lit92 / yingyedays
    avg_svp = svp / yingyedays
    avg_qiyou = qiyou / yingyedays
    avg_chaiyou = chaiyou / yingyedays
    avg_ranyou = ranyou / yingyedays

    # print
    return {"yingyedays":yingyedays,"lit92":lit92, "svp":svp, "qiyou":qiyou,
            "chaiyou":chaiyou, "ranyou":ranyou,"avg_lit92":avg_lit92,
            "avg_svp":avg_svp, "avg_qiyou":avg_qiyou, "avg_chaiyou":avg_chaiyou, "avg_ranyou":avg_ranyou}

def calculateNotoil(cur,station,t1,t2):
    # 获取营业天数
    cur.execute('''
       SELECT count(DISTINCT SUBSTR(minutes,1,10)) FROM res_notoil where stationID='%s' and minutes>='%s' and minutes<'%s'
       ''' % (station, t1,t2))
    # yingyedays=0
    try:
        yingyedays = cur.fetchall()[0][0]
        # print yingyedays
        if yingyedays==0:
            return {}
    except:
        return {}

    # 计算上个月的总体指标
    cur.execute(sql2 % (t1,t2, station))
    zhibiao = cur.fetchall()
    return {"notoilmoney":float(zhibiao[0][0])/1.13,"yingyedays":yingyedays,"avg_notoilmoney":(float(zhibiao[0][0])/1.13)/yingyedays}
    # 计算平均指标

    # print
    # return {"yingyedays":yingyedays,"lit92":lit92, "svp":svp, "qiyou":qiyou,
    #         "chaiyou":chaiyou, "ranyou":ranyou,"avg_lit92":avg_lit92,
    #         "avg_svp":avg_svp, "avg_qiyou":avg_qiyou, "avg_chaiyou":avg_chaiyou, "avg_ranyou":avg_ranyou}

def zhangdie(num):
    if num>0:
        return "涨 %s"%format(Decimal(num), '.1f')
    else:
        return "跌 %s"%format(Decimal(0-num), '.1f')

def sishewuru(num):
    # print "dfds:",num
    # num=int(str(num))
    print num
    num10=num/10
    gewei=num%10
    if(gewei>=5):
        num10+=1
    return num10*10

if __name__ == '__main__':
    print "create handle Excel Object"
    obj_handle_excel = HandleExcel()
    f=open("huanbi.txt","w")

    # 分别使用openpyxl和xlwt将数据写入文件
    # obj_handle_excel.run_main_save_to_excel_with_openpyxl()

    db = pymysql.connect(host=mysqlConfig1["host"], port=mysqlConfig1["port"], user=mysqlConfig1["user"],
                         passwd=mysqlConfig1["passwd"], db=mysqlConfig1["dbname"], charset='utf8')
    cur = db.cursor()

    # 计算会员
    # 北京会员
    cur.execute('''
           SELECT count(DISTINCT SUBSTR(minutes,1,10)) FROM res_oil where minutes>='%s' and minutes<'%s'
           ''' % (yesterday[0:7], today[0:10]))
    yingye=cur.fetchall()[0][0]
    cur.execute('''
          select sum(number) number from res_addVip where date>='%s' and date<'%s'
               ''' % (yesterday[0:7], today[0:10]))
    total_member=cur.fetchall()[0][0]
    # avg_member=total_member/yingye
    cur.execute('''
              select sum(number) number from res_addVip_cd where date>='%s' and date<'%s'
                   ''' % (yesterday[0:7], today[0:10]))
    total_member += cur.fetchall()[0][0]
    avg_member = total_member / yingye


    # 获取油站号
    cur.execute('''
    SELECT DISTINCT stationID FROM res_oil where minutes>='%s' and minutes<'%s'
    '''%(monthago[0:7],today[0:10]))
    stations=cur.fetchall()
    stationList=[ i[0] for i in stations if i[0]!='80004']
    # 计算上个月的
    avg_lit92=0;avg_svp=0;avg_qiyou=0;avg_chaiyou=0;avg_ranyou=0
    avg_notoilmoney=0
    notoilmoney=0
    f.write(u"计算上个月的:\n")
    for station in stationList:
        row=calculateOil(cur,station,monthago[0:7],yesterday[0:7])
        avg_lit92+= row.get("avg_lit92",0)
        avg_svp+= row.get("avg_svp",0)
        avg_qiyou+= row.get("avg_qiyou",0)
        avg_chaiyou+= row.get("avg_chaiyou",0)
        avg_ranyou+= row.get("avg_ranyou",0)
        # f.write(u"station:"+station+",avg_lit92:"+avg_lit92+",avg_svp"+avg_svp+",avg_qiyou:"
        #         +avg_qiyou+",avg_chaiyou:"+avg_chaiyou+",avg_ranyou:"+avg_ranyou)
        # 非油
        notoilrow = calculateNotoil(cur, station, monthago[0:7], yesterday[0:7])
        notoilmoney += notoilrow.get("notoilmoney", 0)
        if(row.get("yingyedays",0)!=0):
            avg_notoilmoney += notoilrow.get("notoilmoney", 0)/row.get("yingyedays",0)
            f.write(u"station:%s,notoilmoney:%f,avg_notoilmoney:%f,days:%d\n" % (
                station, notoilmoney, avg_notoilmoney, row.get("yingyedays", 0)))

    # 计算这个月的
    avg_lit922 = 0;avg_svp2 = 0;avg_qiyou2 = 0;avg_chaiyou2 = 0;avg_ranyou2 = 0
    lit92=0;svp=0;qiyou=0;chaiyou=0;ranyou=0
    # 非油，这个月
    avg_notoilmoney2 = 0
    notoilmoney2 = 0
    f.write(u"计算这个月的:\n")
    for station in stationList:
        row = calculateOil(cur, station, yesterday[0:7], today[0:10])
        avg_lit922 += row.get("avg_lit92", 0)
        avg_svp2 += row.get("avg_svp", 0)
        avg_qiyou2 += row.get("avg_qiyou", 0)
        avg_chaiyou2 += row.get("avg_chaiyou", 0)
        avg_ranyou2 += row.get("avg_ranyou", 0)

        lit92 += row.get("lit92", 0)
        svp += row.get("svp", 0)
        qiyou += row.get("qiyou", 0)
        chaiyou += row.get("chaiyou", 0)
        ranyou += row.get("ranyou", 0)
        # 本月非油
        notoilrow = calculateNotoil(cur, station, yesterday[0:7], today[0:10])
        notoilmoney2 += notoilrow.get("notoilmoney", 0)
        if (row.get("yingyedays", 0) != 0):
            # avg_notoilmoney += notoilrow.get("notoilmoney", 0) / row.get("yingyedays", 0)
            avg_notoilmoney2 +=  notoilrow.get("notoilmoney", 0) / row.get("yingyedays", 0)
            f.write(u"station:%s,notoilmoney:%f,avg_notoilmoney:%f,days:%d\n" % (
                station, notoilmoney2, avg_notoilmoney2, row.get("yingyedays", 0)))
            print "station:", station, "money:", row.get("notoilmoney", 0), "total:", notoilmoney2

    # 每日燃油销量
    oilsales_beijingRBA=0 #北京RBA
    oilsales_beijingCO=0 #北京CO
    oilsales_chengdeRBA=0 #承德RBA
    oilsales_beijingDO=0 #北京do除华成欣
    oilsales=0 #全部除华成欣
    cur.execute(sql4.format(t1=yesterday,t2=today,type='RBA',city='北京'))
    oilsales_beijingRBA=cur.fetchall()[0][0]
    cur.execute(sql4.format(t1=yesterday,t2=today,type='CO',city='北京'))
    oilsales_beijingCO=cur.fetchall()[0][0]
    cur.execute(sql4.format(t1=yesterday,t2=today,type='RBA',city='承德'))
    oilsales_chengdeRBA=cur.fetchall()[0][0]
    cur.execute(sql4.format(t1=yesterday,t2=today,type='DO',city='北京')+' and stationID!=80004')
    oilsales_beijingDO=cur.fetchall()[0][0]

    cur.execute('''select 
        sum(oilLitre) oilLitre
        from res_oil  ro
        where stationID IN 
        (SELECT id from station where id!=80004) and
                 minutes >= '{t1}'
        and minutes< '{t2}' '''.format(t1=yesterday,t2=today))
    oilsales=cur.fetchall()[0][0]
    # print sql4.format(t1=yesterday,t2=today,type='RBA',city='北京')
    # print '北京RBA',oilsales_beijingRBA

    # cur.execute(sql4.format(t1='2019-09-09',t2='2019-09-10',type='RBA',city='北京'))
    # print '北京RBA',cur.fetchall()[0][0]



    f.flush()
    f.close()
    cur.close()

    msg = []

    msg.append(u'注：以下数据截止%s，数据不含华诚欣' % yesterday)
    msg.append(u'环比上月日均数据，全网数据为实际运营天数对应单站总计')
    msg.append(u'%s全网络日均数据(北京+承德+DO站)如下：' % yesterday)
    # msg.append(u'')
    msg.append(u'92# %d 升,环比%s%%' % ( sishewuru(avg_lit922*10)/10, zhangdie(100 * avg_lit922 / avg_lit92 - 100)))
    msg.append(u'SVP(95和98合计) %d 升,环比%s%%' % (sishewuru(avg_svp2*10)/10, zhangdie(100 * avg_svp2 / avg_svp - 100)))
    msg.append(u'汽油合计 %d 升,环比%s%%' % ( sishewuru(avg_qiyou2*10)/10 , zhangdie(100 * avg_qiyou2 / avg_qiyou - 100)))
    msg.append(u'柴油合计 %d 升,环比%s%%' % ( sishewuru(avg_chaiyou2*10)/10 , zhangdie(100 * avg_chaiyou2 / avg_chaiyou - 100)))
    msg.append(u'燃油合计 %d 升,环比%s%%' % ( sishewuru(avg_ranyou2*10)/10 , zhangdie(100 * avg_ranyou2 / avg_ranyou - 100)))
    msg.append(u'便利店合计 %d 元,环比%s%%' % ( sishewuru(avg_notoilmoney2*10)/10 , zhangdie(100 * avg_notoilmoney2 / avg_notoilmoney - 100)))
    msg.append(u'%s月日均新增会员%d人' % (themonth, sishewuru(avg_member*10)/10 ))
    msg.append(u'')
    msg.append(u'%s全网络累计数据(北京+承德+DO站)如下：' % yesterday)
    # print "lit92:",type(lit92),type(lit92*10),qiyou
    msg.append(u'92# %d 升' % (sishewuru(round(Decimal(lit92*10), 1))/10 ))
    msg.append(u'SVP(95和98合计) %d 升' % (sishewuru(round(Decimal(svp*10), 1))/10) )
    msg.append(u'汽油合计 %d 升' % (sishewuru(round(Decimal(qiyou*10), 1))/10) )
    msg.append(u'柴油合计 %d 升' % (sishewuru(round(Decimal(chaiyou*10), 1))/10)  )
    msg.append(u'燃油合计 %d 升' % (sishewuru(round(Decimal(ranyou*10), 1))/10)  )
    msg.append(u'便利店合计 %d 元' % (sishewuru(round(Decimal(notoilmoney2*10), 1))/10)  )
    msg.append(u'%s月累计新增会员%d人' % (themonth, sishewuru(round(Decimal(total_member*10), 1))/10  ))
    msg.append(u'')
    msg.append(u'')
    msg.append(u'')
    msg.append(u'分区域每日燃油销量:%s' %yesterday)
    # msg.append(u'北京RBA：{}'.format(sishewuru(oilsales_beijingRBA*1000)/1000 ) )
    msg.append(u'北京RBA：{}'.format(oilsales_beijingRBA) )
    # msg.append(u'北京CO：{}'.format(sishewuru(oilsales_beijingCO*1000)/1000) )
    msg.append(u'北京CO：{}'.format(oilsales_beijingCO) )
    # msg.append(u'北京RBA＋CO：{}'.format((sishewuru(oilsales_beijingCO*1000)/1000+sishewuru(oilsales_beijingRBA*1000)/1000 )) )
    msg.append(u'北京RBA＋CO：{}'.format(oilsales_beijingCO+oilsales_beijingRBA) )
    # msg.append(u'承德RBA：{}' .format(sishewuru(oilsales_beijingRBA*1000)/1000) )
    msg.append(u'承德RBA：{}' .format(oilsales_chengdeRBA) )
    # msg.append(u'北京Do站(不含华成欣)：{}'.format(sishewuru(oilsales_beijingDO*1000)/1000) )
    msg.append(u'北京Do站(不含华成欣)：{}'.format(oilsales_beijingDO) )
    # msg.append(u'全网络(不含华成欣)：{}' .format(sishewuru(oilsales*1000)/1000) )
    msg.append(u'全网络(不含华成欣)：{}' .format(oilsales) )

    # for i in msg:
    #     print i
    #
    # print msg format(Decimal(2.55), '.1f')
    obj_handle_excel.write_to_excel2(msg,  fname)



e=datetime.datetime.now()
print e-s

